<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class HT_Model_administrator_models_location extends Zend_Db_Table {//ten class fai viet hoa

    protected $_db;

    public function __construct() {
        $this->_name = "location";
        $this->_db = Zend_Registry::get('dbMain');
        parent::init();
    }

    public function convertLocation() {
        $sql = "SELECT * FROM commune";
        $communes = $this->_db->fetchAll($sql);
        foreach ($communes as $commune) {
            $commune_id = $commune['commune_id'];
            $commune_name = $commune['commune_name'];

            $data = array();
            $data['location_name'] = "Trạm y tế " . $commune_name;
            $data['address'] = '';
            $data['commune_id'] = $commune_id;
            $data['location_type_id'] = 1;

            $this->insert($data);
        }
    }

    public function addLocationService($locationId, $serviceId) {
        $this->_name = "location_service";
        $data = array('location_id' => $locationId, 'service_id' => $serviceId);
        $this->insert($data);
        $this->_name = "location";
        return true;
    }

    public function removeLocationService($locationId, $serviceId) {
        $this->_name = "location_service";
        $this->delete('location_id=' . (int) $locationId . ' AND service_id=' . (int) $serviceId);
        $this->_name = "location";
        return true;
    }

    public function addData($data) {
        $this->insert($data);
        return $this->getMaxId();
    }

    public function getMaxId() {
        $sql = "SELECT MAX(location_id) FROM location";
        return (int) $this->_db->fetchOne($sql);
    }

    public function getLocation($locationId, $filter = array()) {
        $sql = " SELECT lc.*, cmm.commune_id, cmm.commune_name, dt.district_id, dt.district_name, pv.province_id, pv.province_name
				FROM location lc
				INNER JOIN commune cmm ON lc.commune_id = cmm.commune_id
				INNER JOIN district dt ON cmm.district_id = dt.district_id
				INNER JOIN province pv ON dt.province_id = pv.province_id
				WHERE lc.location_id= " . (int) $locationId;
        return $this->_db->fetchRow($sql);
    }

    public function getLocationServices($locationId) {
        $services = array();
        $ids = null;
        $addedServices = $this->_getAddedServices($locationId);
        foreach ($addedServices as $sv) {
            $ids .= $sv['serviceId'] . ',';
        }
        if ($ids)
            $ids = substr($ids, 0, -1);

        $services['location'] = $this->getLocation($locationId);
        ;
        $services['addedServices'] = $addedServices;
        $services['availableServices'] = $this->_getAvailableServices($ids);
        ;
        return $services;
    }

    private function _getAvailableServices($ids) {
        $sqlPlus = null;
        if ($ids)
            $sqlPlus = " AND id not in ($ids) ";
        $sql = "SELECT id serviceId, ser_name serviceName
				FROM service
				WHERE type=2 $sqlPlus";
        return $this->_db->fetchAll($sql);
    }

    private function _getAddedServices($locationId) {
        $sql = "SELECT sv.id serviceId, sv.ser_name serviceName
				FROM location_service ls
				INNER JOIN  service sv ON sv.id = ls.service_id
				WHERE sv.type=2 AND location_id= " . (int) $locationId;
        return $this->_db->fetchAll($sql);
    }

    public function getListLocation_nb($filter = array()) {
        $sqlPlus = $this->getListLocation_sqlPlus($filter);
        $sql = "SELECT COUNT(lc.location_id)
				FROM location lc
				INNER JOIN commune cmm ON lc.commune_id = cmm.commune_id
				INNER JOIN district dt ON cmm.district_id = dt.district_id
				INNER JOIN province pv ON dt.province_id = pv.province_id
				WHERE 1=1 $sqlPlus ";
        return $this->_db->fetchOne($sql);
    }

    public function getListLocation($start = 0, $size = 10, $filter = array()) {
        $sqlPlus = $this->getListLocation_sqlPlus($filter);
        $sql = "SELECT lc.*, cmm.commune_name, dt.district_name, pv.province_name
				FROM location lc
				INNER JOIN commune cmm ON lc.commune_id = cmm.commune_id
				INNER JOIN district dt ON cmm.district_id = dt.district_id
				INNER JOIN province pv ON dt.province_id = pv.province_id
				WHERE 1=1 $sqlPlus ORDER BY is_partner DESC, location_name ASC LIMIT $start,$size";
        return $this->_db->fetchAll($sql);
    }

    public function getListLocationPartner() {
        $sql = "SELECT lc.*, cmm.commune_name, dt.district_name, pv.province_name
                FROM location lc
                INNER JOIN commune cmm ON lc.commune_id = cmm.commune_id
                INNER JOIN district dt ON cmm.district_id = dt.district_id
                INNER JOIN province pv ON dt.province_id = pv.province_id
                WHERE is_partner = 1 ORDER BY location_name";
        return $this->_db->fetchAll($sql);
    }

    private function getListLocation_sqlPlus($filter) {
        $sqlPlus = null;
        foreach ((array) $filter as $key => $val) {
            $key = trim($key);
            $val = trim($val);
            switch ($key) {
                case 'keyword':
                    if ($val) {
                        $sqlPlus .= " AND (lc.location_name LIKE '%$val%' OR
						lc.address LIKE '%$val%' OR
						lc.phone LIKE '%$val%' OR
						lc.hotline LIKE '%$val%' OR
						lc.email LIKE '%$val%' OR
						lc.website LIKE '%$val%') ";
                    }
                    break;
                case 'location_type_id':
                    if ($val > 0)
                        $sqlPlus .= " AND lc.location_type_id = " . (int) $val;
                    break;
                case 'province_id':
                    if ($val > 0)
                        $sqlPlus .= " AND pv.province_id = " . (int) $val;
                    break;
                case 'district_id':
                    if ($val > 0)
                        $sqlPlus .= " AND dt.district_id = " . (int) $val;
                    break;
                case 'commune_id':
                    if ($val > 0)
                        $sqlPlus .= " AND cmm.commune_id = " . (int) $val;
                    break;
            }
        }
        return $sqlPlus;
    }

    /**
     * get localtion
     */
    public function getLocationId($idlocaltion) {
        $select = $this->_db->select()
                ->from('location', '*')
                ->where('location_id = ?', $idlocaltion);
        $row = $select->query()->fetchObject();
        return $row;
    }

    public function getFeature() {
        $select = $this->_db->select()
                ->from('news_tags', '*')
                ->where('is_feature = 1');
        $row = $select->query()->fetchObject();
        return $row;
    }
    public function getAll($where){
        $sql = "select * from location where ".$where;
        $retval = $this->_db->fetchAll($sql);
        return $retval;
    }
    public function getAllCondition($where){
        $sql = "select Lo.*, Pa.rate, Pa.image, Pa.link, Pa.partner_name, Pa.id as partner_id from location Lo left join partners Pa on Lo.location_id = Pa.location_id where ".$where;

        $retval = $this->_db->fetchAll($sql);
        return $retval;
    }
    
    public function findById($id){
        $sql = "select * from location where location_id = $id";
        $retval = $this->_db->fetchRow($sql);
        return $retval;
    }

}

?>
